<?php
require('common.php');

$instance = sql_row1("SELECT * FROM Instances WHERE User='$user' AND Domain='$domain' AND Line_Number='$line'");
//die(var_dump($instance));
$bonus_field = $instance['Bonus_Field'];
//die("bonus field: $bonus_field");
$table = store_in_table_table($bonus_field);

// an HTML form dropdown with all the available variables
// $name is the "name" tag in the <select> statement
// and should also be the field name
function variables_dropdown($name, $required)
{
	global $user, $domain, $bonus_field;
	$result = sql_exec("SELECT * FROM Variables WHERE (ID > -1 AND Domain='$domain' AND User='$user') AND Name IS NOT NULL ORDER BY ID DESC");
	$form_fields_sql = add_form_fields(); // if there are no form fields, this fxn will return null string
	if (mysql_num_rows($result) > 0 || !empty($form_fields_sql) )
	//if (mysql_num_rows($result) > 0)
	{
		$dropdown = "<select name=\"$name\">\n";
		
		// if this column is NULL, stick NULL in the top of the dropdown
		if ($required == 'Optional')
		{
			$dropdown .= '<option value="NULL">(No Value)' . "</option>\n";
		} 

		// retrieve the previously selected variable for this field
		// retrieves something like $var8
		$selected_value = selected_at_field($name, $bonus_field);
		// we want just 8
		$selected_value_id = substr($selected_value, strlen('$var'));
		//die("$selected_value_id");

		while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
		{
			$selected_toggle = ($selected_value_id == $row['ID']) ? " selected " : "";
			$dropdown .= '<option ' . $selected_toggle . 'value="' . $row['ID'] . '">' . $row['Name'] . "</option>\n";
		}
		
		
		// now add the form variables, if they exist
		if (!empty($form_fields_sql))
		{
			$result = sql_exec($form_fields_sql);
			while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
			{
				$field_name = $row['Var1_Text'];
				$field_name_to_user = $field_name . '@form';
				// use substr because $selected_value is like $color but we want color
				$selected_toggle = ( substr($selected_value, 1) == $field_name) ? " selected " : "";
				
				$dropdown .= '<option ' . $selected_toggle . 'value="' . $field_name . '">' . $field_name_to_user . "</option>\n";
			}
		}
		
		$dropdown .= "</select>\n";
		return $dropdown;
	} else {
		return ''; // no variables
	}
}

// finds the variable selected for the specified column 
function selected_at_field($field, $bonus_field)
{
	$bonus_field = substr($bonus_field, $lob_off_front);
	$end_point = strpos($bonus_field, ' SET ');
	$settings = substr($bonus_field, $end_point+strlen(' SET '));
	$settings_array = explode(',', $settings);
	//die(var_dump($settings_array));
	$first_half = $field . '=';
	//die("$first_half");
	foreach ($settings_array as $setting)
	{
		if (strpos($setting, $first_half) !== FALSE)
		{
			$second_half = substr($setting, strpos($setting, '=')+1);
			$second_half = str_replace("'", '', $second_half);
			//die("$second_half");
			return $second_half;
		}
	}
}

// list all of the fields in this table as an ordered list
function all_fields($table)
{
	$result = sql_exec("SHOW COLUMNS FROM $table");
	
	if (mysql_num_rows($result) > 0)
	{
		$dropdown = "<ol>\n";
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
		{
			$field_name = $row['Field'];
			if ($field_name != 'ID')
			{
				$required = ($row['Null'] == "NO") ? "Required" : "Optional";
				$vars_dropdown = variables_dropdown($field_name, $required);
				// if there are no variables, we have a problem.  return null string.
				if (empty($vars_dropdown))
				{
					return '';
				}

				switch ($row['Type']) {
				case 'int(11)':
					$type = "Integer";
					break;
				case 'text':
					$type = "Text";
					break;
				}

				$dropdown .= "<li>$field_name ($type) ($required): $vars_dropdown</li>\n";
			}
		}
		$dropdown .= "</ol>\n";
		return $dropdown;
	} else {
		return ''; // table is empty
	}
}

// if the form was submitted, store the values
if (!empty($G['save']))
{
	// remove the 'submit' value so it doesn't confuse us later
	unset($G['save']);
	unset($G['table']);
	unset($G['line']);
	unset($G['domain']);

	$sql = "INSERT INTO $table SET ";
	foreach ($G as $field=>$variable)
	{
		if ($variable == 'NULL') 
		{
			$sql .= "$field=$variable, ";
		} elseif (is_numeric($variable)) {
			$variable = '$var' . $variable;
			$sql .= "$field='$variable', ";
		} else {
			// in this case it should be a form variable
			// array references are a royal pain in the ass and can't be stuck in an 
			// sql query string like a normal variable, so 
			// divide them from the string and concatenate them with it
			$variable = '$G[\'' . $variable . '\']';
			$sql .= "$field='\" . $variable . \"', ";
		}
	}
	
	// remove the trailing comma
	$sql = substr($sql, 0, strlen($sql)-2);
	$sql = mysql_real_escape_string($sql);

	// store it in the instance
	sql_exec("UPDATE Instances SET Bonus_Field='$sql' WHERE User='$user' AND Domain='$domain' AND Line_Number='$line'");
	update($user, $domain, $line);
	header("Location: edit_page.php?domain=$domain");
}

html_header("edit a table");
?>

<body>

<div id="container">

<?php header_div(); ?>

<div id="body">

<h4>store in table</h4>

<?php $all_fields =  all_fields($table); 

if (!empty($all_fields))
{
?>

</p>

<form action="store_in_table.php" method="get">
<p>
<?php echo $all_fields; ?>
<input type="hidden" name="table" value="<?php echo $table ?>" />
<input type="hidden" name="domain" value="<?php echo $domain; ?>" />
<input type="hidden" name="line" value="<?php echo $line; ?>" />
<p><input type="submit" name="save" value="save" /></p>
</form>
<?php } else { ?>
<p>There appear to be no tables, no variables, or both.</p>
<?php } ?>

<p>
<form action="delete_instance.php" method="get">
<input type="hidden" name="domain" value="<?php echo $domain; ?>" />
<input type="hidden" name="line" value="<?php echo $line; ?>" />
<input type="submit" value="delete">
</form>
</p>

<?php echo footer(); ?>

</div>

</div>
</body>
</html>
